using System;
using System.Web;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using MandalaySoft.Common;

/// <summary>
/// Summary description for MyDatabase
/// Database Function(Update, Delete, Insert, Select) for all class
/// 
/// </summary>
/// 
namespace MandalaySoft.Common
{
    public class MyDatabase
    {
        private string _connectionString;
        private SqlConnection _sqlCon;

        public void CloseConnection()
        {
            if (_sqlCon.State != ConnectionState.Closed)
            {
                _sqlCon.Close();
                _sqlCon = null;
            }
        }

        public void OpenConnection()
        {
            _sqlCon = new SqlConnection(_connectionString);
            _sqlCon.Open();
        }

        public MyDatabase()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["MandalaySoft.MainSqlConnString"].ConnectionString;
        }

        public MyDatabase(string ConnectionStringName)
        {
            _connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
        }

        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        public bool ColumnExists(IDataReader reader, string columnName)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                if (reader.GetName(i) == columnName)
                {
                    return true;
                }
            }

            return false;
        }

        public SqlDataReader GetSqlDataReader(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)
        {
            SqlDataReader _sqlreader = null;
            SqlCommand _sqlcmd = null;
            error = new MyError();
            try
            {
                OpenConnection();
                _sqlcmd = new SqlCommand(sqlStatement, _sqlCon);
                _sqlcmd.CommandType = cmdType;
                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }
                _sqlreader = _sqlcmd.ExecuteReader();
            }
            catch (SqlException sqlEx)
            {
                error.ErrorNo = sqlEx.ErrorCode;
                error.ErrorMessage = sqlEx.Message;
                error.ErrorStack = sqlEx.StackTrace;
                error.ErrorLocation = "sqlStatement: " + sqlStatement + " at SqlDataReader GetSqlDataReader(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)";
                if (sqlEx.Number < 50000)
                {
                    MyError.CreateSqlExceptionLog(sqlEx, "SqlDataReader GetSqlDataReader(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)", sqlStatement);
                }
            }
            catch (Exception Ex)
            {
                error.ErrorNo = 100001;
                error.ErrorMessage = Ex.Message;
                error.ErrorStack = Ex.StackTrace;
                error.ErrorLocation = "sqlStatement: " + sqlStatement + " at SqlDataReader GetSqlDataReader(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)";
                MyError.CreateExceptionLog(Ex, "GetSqlDataReader(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)");
            }

            return _sqlreader;
        }

        public DataTable GetDataTable(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)
        {
            DataTable _datatable = null;
            SqlCommand _sqlcmd = null;
            SqlDataAdapter _sqlda = null;
            error = new MyError();
            try
            {
                OpenConnection();
                _sqlcmd = new SqlCommand(sqlStatement, _sqlCon);
                _sqlcmd.CommandType = cmdType;
                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }
                _sqlda = new SqlDataAdapter(_sqlcmd);
                _datatable = new DataTable();
                _sqlda.Fill(_datatable);
            }
            catch (SqlException sqlEx)
            {
                error.ErrorNo = sqlEx.ErrorCode;
                error.ErrorMessage = sqlEx.Message;
                error.ErrorStack = sqlEx.StackTrace;
                error.ErrorLocation = "sqlStatement: " + sqlStatement + " at Execute(string sqlStatement, CommandType cmdType, SortedList sqlPara, MyError error)";
                if (sqlEx.ErrorCode < 50000)
                {
                    MyError.CreateSqlExceptionLog(sqlEx, "DataTable GetDataTable(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)", sqlStatement);
                }
            }
            catch (Exception Ex)
            {
                error.ErrorNo = 100001;
                error.ErrorMessage = Ex.Message;
                error.ErrorStack = Ex.StackTrace;
                error.ErrorLocation = "sqlStatement: " + sqlStatement + " at Execute(string sqlStatement, CommandType cmdType, SortedList sqlPara, MyError error)";
                MyError.CreateExceptionLog(Ex, "DataTable GetDataTable(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlda != null)
                {
                    _sqlda = null;
                }

                CloseConnection();
            }

            return _datatable;
        }

        public DataTable GetDataTableXml(string xmlFilePath, out MyError error)
        {
            DataTable _datatable = null;
            error = new MyError();
            try
            {
                _datatable.ReadXml(xmlFilePath);
            }
            catch (Exception Ex)
            {
                error.ErrorNo = 10001;
                error.ErrorMessage = Ex.Message;
                error.ErrorStack = Ex.StackTrace;
                error.ErrorLocation = "xmlFilePath: " + xmlFilePath + " at  GetDataTableXml(string xmlFilePath, out MyError error)";
                MyError.CreateExceptionLog(Ex, " GetDataTableXml(string xmlFilePath, out MyError error)");
            }
            finally
            {

            }

            return _datatable;
        }

        public DataSet GetDataSet(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)
        {
            DataSet _dataset = null;
            SqlCommand _sqlcmd = null;
            SqlDataAdapter _sqlda = null;
            error = new MyError();
            try
            {
                OpenConnection();
                _sqlcmd = new SqlCommand(sqlStatement, _sqlCon);
                _sqlcmd.CommandType = cmdType;
                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }
                _sqlda = new SqlDataAdapter(_sqlcmd);
                _dataset = new DataSet();
                _sqlda.Fill(_dataset);
            }
            catch (SqlException sqlEx)
            {
                error.ErrorNo = sqlEx.ErrorCode;
                error.ErrorMessage = sqlEx.Message;
                error.ErrorStack = sqlEx.StackTrace;
                error.ErrorLocation = "sqlStatement: " + sqlStatement + " at Execute(string sqlStatement, CommandType cmdType, SortedList sqlPara, MyError error)";
                if (sqlEx.Number < 50000)
                {
                    MyError.CreateSqlExceptionLog(sqlEx, "GetDataSet(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)", sqlStatement);
                }
            }
            catch (Exception Ex)
            {
                error.ErrorNo = 10001;
                error.ErrorMessage = Ex.Message;
                error.ErrorStack = Ex.StackTrace;
                error.ErrorLocation = "sqlStatement: " + sqlStatement + " at Execute(string sqlStatement, CommandType cmdType, SortedList sqlPara, MyError error)";
                MyError.CreateExceptionLog(Ex, "GetDataSet(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                if (_sqlda != null)
                {
                    _sqlda = null;
                }

                CloseConnection();
            }
            return _dataset;
        }

        public Int64 Execute(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)
        {
            Int64 _result = -1;
            SqlCommand _sqlcmd = null;
            error = new MyError();

            try
            {
                OpenConnection();
                _sqlcmd = new SqlCommand();
                _sqlcmd.CommandType = cmdType;
                _sqlcmd.CommandText = sqlStatement;
                _sqlcmd.Connection = _sqlCon;

                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }

                DbParameter myReturnValue = null;
                myReturnValue = _sqlcmd.CreateParameter();
                myReturnValue.Direction = ParameterDirection.ReturnValue;
                _sqlcmd.Parameters.Add(myReturnValue);

                _sqlcmd.ExecuteNonQuery();
                _result = Convert.ToInt64(myReturnValue.Value);
                return _result;
            }
            catch (SqlException sqlEx)
            {
                error.ErrorNo = sqlEx.ErrorCode;
                error.ErrorMessage = sqlEx.Message;
                error.ErrorStack = sqlEx.StackTrace;
                error.ErrorLocation = "sqlStatement: " + sqlStatement + " at Execute(string sqlStatement, CommandType cmdType, SortedList sqlPara, MyError error)";
                if (sqlEx.Number < 50000)
                {
                    MyError.CreateSqlExceptionLog(sqlEx, "Execute(string sqlStatement, CommandType cmdType, SortedList sqlPara)", sqlStatement);
                }
            }
            catch (Exception Ex)
            {
                error.ErrorNo = 100001;
                error.ErrorMessage = Ex.Message;
                error.ErrorStack = Ex.StackTrace;
                error.ErrorLocation = "sqlStatement: " + sqlStatement + " at Execute(string sqlStatement, CommandType cmdType, SortedList sqlPara, MyError error)";
                MyError.CreateExceptionLog(Ex, "Execute(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                CloseConnection();
            }
            return _result;
        }

        public int ExecuteNonSql(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)
        {
            int _affCount = -1;
            SqlCommand _sqlcmd = null;
            error = new MyError();
            try
            {
                OpenConnection();
                _sqlcmd = new SqlCommand();
                _sqlcmd.CommandType = cmdType;
                _sqlcmd.CommandText = sqlStatement;
                _sqlcmd.Connection = _sqlCon;

                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }

                _affCount = _sqlcmd.ExecuteNonQuery();

                return _affCount;
            }
            catch (SqlException sqlEx)
            {
                error.ErrorNo = sqlEx.ErrorCode;
                error.ErrorMessage = sqlEx.Message;
                error.ErrorStack = sqlEx.StackTrace;
                error.ErrorLocation = "sqlStatement: " + sqlStatement + " at ExecuteNonSqlSP(string sqlStatement, SortedList sqlPara, out MyError error)";
                if (sqlEx.Number < 50000)
                {
                    MyError.CreateSqlExceptionLog(sqlEx, "ExecuteNonSqlSP(string sqlStatement, SortedList sqlPara, out MyError error)", sqlStatement);
                }
            }
            catch (Exception Ex)
            {
                error.ErrorNo = 100001;
                error.ErrorMessage = Ex.Message;
                error.ErrorStack = Ex.StackTrace;
                error.ErrorLocation = "sqlStatement: " + sqlStatement + " at ExecuteNonSqlSP(string sqlStatement, SortedList sqlPara, out MyError error)";
                MyError.CreateExceptionLog(Ex, "ExecuteNonSqlSP(string sqlStatement, SortedList sqlPara, out MyError error)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                CloseConnection();
            }
            return _affCount;
        }

        public object ExecuteScalar(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)
        {
            object _object = null;
            SqlCommand _sqlcmd = null;
            error = new MyError();
            try
            {
                OpenConnection();
                _sqlcmd = new SqlCommand(sqlStatement, _sqlCon);
                _sqlcmd.CommandType = cmdType;

                foreach (DictionaryEntry dPara in sqlPara)
                {
                    _sqlcmd.Parameters.AddWithValue(dPara.Key.ToString(), dPara.Value);
                }

                _object = _sqlcmd.ExecuteScalar();
            }
            catch (SqlException sqlEx)
            {
                error.ErrorNo = sqlEx.ErrorCode;
                error.ErrorMessage = sqlEx.Message;
                error.ErrorStack = sqlEx.StackTrace;
                error.ErrorLocation = "sqlStatement: " + sqlStatement + " at ExecuteScalar(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)";
                if (sqlEx.Number < 50000)
                {
                    MyError.CreateSqlExceptionLog(sqlEx, "ExecuteScalar(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)", sqlStatement);
                }
            }
            catch (Exception Ex)
            {
                error.ErrorNo = 10001;
                error.ErrorMessage = Ex.Message;
                error.ErrorStack = Ex.StackTrace;
                error.ErrorLocation = "sqlStatement: " + sqlStatement + " at ExecuteScalar(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)";
                MyError.CreateExceptionLog(Ex, "ExecuteScalar(string sqlStatement, CommandType cmdType, SortedList sqlPara, out MyError error)");
            }
            finally
            {
                if (_sqlcmd != null)
                {
                    _sqlcmd = null;
                }

                CloseConnection();
            }
            return _object;
        }
    }
}